/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package functions;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import model.Database;
import model.Diagnose;
import model.Dog;
import model.ReportTypes.*;
import model.User;
import stuff.FDate;

/**
 *
 * @author JArik
 */
public class Reports {


    public static ArrayList<VakcineReport> getVakcineReport() throws SQLException
    {
        Database db = new Database();
        ArrayList<VakcineReport> rep = new ArrayList<VakcineReport>();
        ResultSet set = db.getDBInfo(
                "SELECT U.id, U.name, U.surname, U.address, DSD.dogId, DOG.age, DOG.breed, DOG.nick, D.name AS vakcName, D.type, DSD.date from dogs DOG, users U, "+
                     "(SELECT SD.id, DD.dogId, DD.date from discharged_drugs DD join sklad_drugs SD on DD.drugId = SD.rec_id where DD.deleted != 1) DSD "+
                     "join drugs D ON DSD.id = D.id WHERE D.type = 'VAKC' AND DOG.id = DSD.dogId AND DOG.userId = U.id "+
                     "ORDER BY DSD.date DESC,U.name ASC");
        VakcineReport vakc;
        try
        {
            while(set.next())
            {
                vakc = new VakcineReport();
                vakc.setDog(new Dog(set.getInt(5),set.getDate(6),set.getInt(1), set.getString(7), set.getString(8)));
                User user = new User(set.getInt(1),set.getString(2), set.getString(3),set.getInt(5),set.getString(7),set.getString(8));
                user.setAddress(set.getString(4));
                vakc.setUser(user);
                vakc.setVakcDate(set.getDate(11));
                Calendar vakcDat = Calendar.getInstance();
                vakcDat.setTimeInMillis(set.getDate(11).getTime());
                vakcDat.add(Calendar.YEAR, 1);
                FDate neVakc = new FDate(vakcDat.getTimeInMillis());
                vakc.setNextVakcDate(neVakc);
                vakc.setVakcineName(set.getString(9));
                vakc.setVakcineType(set.getString(10));
                rep.add(vakc);
            }
        }catch(Exception e)
        {
            LogError.reportSilentError(e);
            e.printStackTrace();
        }finally
        {
            db.closeStatement();
            return rep;
        }
    }

    public static VisitOverallReport getVisitOverallReport(Date dateFrom, Date dateTo) throws SQLException
    {
        Database db = new Database();
        VisitOverallReport allRep = new VisitOverallReport();

        // drugs
        String query = "select SUM(payed), ROUND(SUM(SD.buy*DD.amount),2) from discharged_drugs DD join sklad_drugs SD on DD.drugId = SD.rec_id " +
                "where DD.deleted != 1 ";
        if(dateTo != null)
        {
            dateTo= new Date(dateTo.getTime());

            if(dateFrom != null)
              dateFrom = new Date(dateFrom.getTime());
            
            query = query.concat(" AND DD.date BETWEEN '"+dateFrom+"' AND '"+dateTo+"' ");
        }
        
        
        ResultSet set = db.getDBInfo(query);
        if(set.next())
        {
            allRep.setDrugsTotalSell(set.getDouble(1));
            allRep.setDrugsTotalBuy(set.getDouble(2));
        }
        db.closeStatement();

        // operations
        query = "select SUM(payed) from operations_made OM where OM.deleted != 1 ";
        if(dateTo != null)
        {
            query = query.concat(" AND date BETWEEN '"+dateFrom+"' AND '"+dateTo+"' ");
        }

        set = db.getDBInfo(query);
        if(set.next())
        {
            allRep.setOperTotalSell(set.getDouble(1));
        }
        db.closeStatement();

        // visits
        query = "select SUM(visitPricePayed),SUM(visitOtherPayed) from visits where deleted != 1";
        if(dateTo != null)
        {
            query = query.concat(" AND date BETWEEN '"+dateFrom+"' AND '"+dateTo+"' ");
        }

        set = db.getDBInfo(query);
        if(set.next())
        {
            allRep.setVisitTotalPrice(set.getDouble(1));
            allRep.setVisitOtherTotalPrice(set.getDouble(2));
        }
        db.closeStatement();
        return allRep;
        
    }

    public static ArrayList<ProfitReport> getProfitReport(Date fromDate, Date toDate) throws SQLException
    {
         Database db = new Database();
         
         
         String query = "select AL.*, SUM(OM.payed) from operations_made OM right join "+
                        "(select VD.*, SUM(VD.payed), SUM(SD.buy*VD.amount) AS SUMbuy from sklad_drugs SD right join "+
                        "(select V.id, V.visitPricePayed, V.visitOtherPayed, V.date, DD.drugId, DD.amount, DD.payed "+
                        "from visits V left join discharged_drugs DD ON DD.visitId = V.id) VD "+
                        "ON SD.rec_id = VD.drugId GROUP BY VD.id) AL "+
                        "ON AL.id = OM.visitId ";
         if(toDate != null)
         {
             toDate= new Date(toDate.getTime());
             if(fromDate != null)
                 fromDate = new Date(fromDate.getTime());
             query = query.concat(" where AL.date BETWEEN '"+fromDate+"' AND '"+toDate+"' ");
         }
         query = query.concat(" GROUP BY AL.id");
         ArrayList<ProfitReport> vRep = new ArrayList<ProfitReport>();
         ProfitReport rep;
         ResultSet set = db.getDBInfo(query);

         try
         {
             while(set.next())
             {
                 rep = new ProfitReport();
                 rep.setVisitId(set.getInt(1));
                 rep.setVisitPrice(set.getDouble(2));
                 rep.setOtherPrice(set.getDouble(3));
                 rep.setDate(set.getDate(4));
                 rep.setDrugsPrice(set.getDouble(8));
                 rep.setDrugsBuyPrice(set.getDouble(9));
                 rep.setOperPrice(set.getDouble(10));
                 vRep.add(rep);
             }
         } catch(Exception e)
         {
             LogError.reportSilentError(e);
             e.printStackTrace();
         } finally
         {
             db.closeStatement();
             return vRep;
         }
    }

    public static ArrayList<ArrayList<VisitReport>> getVisitReport(Date fromDate, Date toDate) 
            throws SQLException
    {
        Database db = null;
        String dateLimit = " WHERE 1=1 ";
        if(fromDate != null && toDate != null)
            dateLimit += " AND date BETWEEN '"+fromDate.toString() + "' AND '" + toDate.toString() +"' ";
        
        String query =
           " SELECT U.uId, U.userSurname, U.address, V.date, " +
           " SUM(V.visitPricePayed) as visitPricePayed, " +
           " SUM(V.visitOtherPayed) as visitOtherPayed, V.cardPayment, "+
           " SUM((SELECT SUM(payed) FROM discharged_drugs DD WHERE DD.visitId=V.id AND DD.deleted=0 )) AS drugPayed, "+
           " SUM((SELECT SUM(payed/(SELECT 1+tax/100 FROM sklad_drugs SD WHERE SD.rec_id=DD.drugId)) " +
           "         FROM discharged_drugs DD WHERE DD.visitId=V.id AND DD.deleted=0 )) AS drugTaxed, "+
           " SUM((SELECT SUM(payed) FROM operations_made OM WHERE OM.visitId=V.id AND OM.deleted=0)) AS operPayed "+
           " FROM visits V LEFT JOIN "+
                        " (SELECT dogs.id, users.id AS uId, "+
                        "         users.surname as userSurname, "+
                        "		  users.address FROM users RIGHT JOIN dogs  ON dogs.userId=users.id) U "+
                        " ON V.dogId=U.id "+ dateLimit + 
                        " GROUP BY U.uId, V.date, V.cardPayment ORDER BY DATE DESC,uId DESC ";

        ArrayList<VisitReport> rep = new ArrayList<VisitReport>();
        ArrayList<ArrayList<VisitReport>> dayRep = new ArrayList<ArrayList<VisitReport>>();
        VisitReport vis=null;
        try
        {
            Dogy.incrLoadingProgress(10,"query","Обращение к базе данных");
            db = new Database();
            Dogy.incrLoadingProgress(25,"query","Выборка данных");
            ResultSet set = db.getDBInfo(query);
            Dogy.incrLoadingProgress(50,"query","Обработка данных");

            while(set.next())
            {
                  if(vis == null || !vis.getDate().equals(set.getDate("date")))
                  {
                      rep = new ArrayList<VisitReport>();
                      dayRep.add(rep);
                  }
                  vis = new VisitReport();
                  User user = new User(set.getInt("uId"),
                                       "", set.getString("userSurname"));
                  user.setAddress(set.getString("address"));

                  /*vis.setUserId(set.getInt("uId"));
                  vis.setUserName(set.getString("userName"));
                  if(set.getString("address") != null &&
                          set.getString("address").split("\r\n").length > 0)
                  vis.setAddress(set.getString("address").split("\r\n")[0]);*/
                  vis.setUser(user);
                  vis.setDate(set.getDate("date"));
                  vis.setVisitPrice(Dogy.roundTwoDecimals(set.getDouble("visitPricePayed") + set.getDouble("visitOtherPayed")));
                  vis.setDrugPrice(Dogy.roundTwoDecimals(set.getDouble("drugPayed")));
                  vis.setDrugTaxed(Dogy.roundTwoDecimals(set.getDouble("drugTaxed")));
                  vis.setOperPrice(Dogy.roundTwoDecimals(set.getDouble("operPayed")));
                  vis.setPayedByCard(set.getBoolean("cardPayment"));
                  rep.add(vis);
            }
            Dogy.incrLoadingProgress(100,"query","Обработка данных");
        } catch(Exception e)
        {
            LogError.reportSilentError(e);
            e.printStackTrace();
        } finally
        {
            if(db != null)
              db.closeStatement();
            return dayRep;
        }
    }

    public static ArrayList<DiagnoseReport> getDiagReport(Date fromDate, Date toDate, boolean withPredv)
            throws SQLException
    {
        Database db = new Database();
        ArrayList<DiagnoseReport> diags = new ArrayList<DiagnoseReport>();
        String queryLimit = " WHERE 1=1 ";
        if(fromDate != null && toDate != null)
            queryLimit += " AND date BETWEEN '"+fromDate.toString()+"' AND '"+toDate.toString() +"' ";
        if(withPredv == false)
            queryLimit += " AND diagStatus=0 ";
        ResultSet set = db.getDBInfo("SELECT D.name, D.rusName, COUNT(diagId) AS amount, DM.date" +
                                     " FROM diagnoses_made  DM LEFT JOIN diagnoses D ON " +
                                     " D.id=DM.diagId "+queryLimit+" GROUP BY DM.diagId");
        DiagnoseReport rep;
        Diagnose diag;
        try
        {
            while(set.next())
            {
                rep = new DiagnoseReport();
                diag = new Diagnose();
                diag.setName(set.getString("name"));
                diag.setRusName(set.getString("rusName"));
                rep.setAmount(set.getInt("amount"));
                rep.setDiagnose(diag);
                diags.add(rep);
            }
        }catch(Exception e)
        {
            LogError.reportSilentError(e);
            e.printStackTrace();
        } finally
        {
            db.closeStatement();
            return diags;
        }
    }

}
